<?php
/**
 * Copyright (c) 2009, 新浪桌面 - 视频
 * All rights reserved.
 *
 * 名    称：DB_mysql.php
 * 摘    要：数据库读写基础类
 * 作    者：李俊杰
 * 版    本：1.0
 */

class MysqlDB
{

	/**
	 * mysql函数功能映射表
	 *
	 * @var array
	 */
	var $functions = array(
		'connect'            => 'mysql_connect',
		'pconnect'           => 'mysql_pconnect',
		'selectDB'           => 'mysql_select_db',
		'query'              => 'mysql_query',
		'query_unbuffered'   => 'mysql_unbuffered_query',
		'fetchRow'           => 'mysql_fetch_row',
		'fetchArray'         => 'mysql_fetch_array',
		'fetchField'         => 'mysql_fetch_field',
		'freeResult'         => 'mysql_free_result',
		'dataSeek'           => 'mysql_data_seek',
		'error'              => 'mysql_error',
		'errno'              => 'mysql_errno',
		'affectedRows'       => 'mysql_affected_rows',
		'numRows'            => 'mysql_num_rows',
		'numFields'          => 'mysql_num_fields',
		'fieldName'          => 'mysql_field_name',
		'insertID'           => 'mysql_insert_id',
		'escapeString'       => 'mysql_escape_string',
		'real_escape_string' => 'mysql_real_escape_string',
		'close'              => 'mysql_close',
		'clientEncoding'     => 'mysql_client_encoding',
	);
	/**
	 * 用于fetch_array的数组常量
	 *
	 * @var array
	 */
	var $fetchtypes = array(
		'DBARRAY_NUM'   => MYSQL_NUM,
		'DBARRAY_ASSOC' => MYSQL_ASSOC,
		'DBARRAY_BOTH'  => MYSQL_BOTH
	);
	/**
	 * 数据库名称
	 *
	 * @var String
	 */
	var $database = null;
	/**
	 * 数据库连接
	 *
	 * @var String
	 */
	var $connection = null;
	/**
	 * 最近的SQL字符串查询的内容
	 *
	 * @var String
	 */
	var $sql = '';
	/**
	 * 数据库出错信息的处理方式 true：显示并退出，false：隐藏继续执行
	 *
	 * @var Boolean
	 */
	var $reporterror = true;
	/**
	 * 最近的数据库出错信息
	 *
	 * @var String
	 */
	var $error = '';
	/**
	 * 最近的数据库出错代码
	 *
	 * @var Integer
	 */
	var $errno = '';
	/**
	 * 是否输出Mysql运行出错日志，可以作为Mysql出错的监控的报警日志
	 *
	 * @var Boolean
	 */
	var $logerrors = true;
	/**
	 * Mysql错误日志的路径
	 *
	 * @var String
	 */
	var $errorpath = '/data0/vshare/logs/mysql/';
	/**
	 * 是否记录数据库执行时间
	 *
	 * @var Boolean
	 */
	var $usetime = true;
	/**
	 * 限制连接次数
	 *
	 * @public int
	 */
	public $connTimes = 5;

	/**
	 * 数据库类初始
	 *
	 * @return dataBase
	 */
	function __construct($params)
	{
		//判断是否为debug模式
		if (DEBUG) $this->usetime = TRUE;
		//连接数据库
		$this->configConnect($params);
		return true;
	}

	/**
	 * 数据库连接函数
	 *
	 * @param String $database	要连接的数据库名
	 * @param String $servername	数据库的服务器地址
	 * @param String $username	用户名
	 * @param String $password	密码
	 * @param Integer $port	端口
	 * @param Boolean $usepconnect	是否使用长连接
	 * @param String $charset	链接的Charset MySQL 4.1.13+ 或者 MySQL 5.1.10+
	 */
	function connect($database, $servername, $username, $password, $port = FALSE, $usepconnect = false, $charset = '')
	{
		$pagestarttime = microtime();
		$this->database = $database;
		$port = ($port!==FALSE) ? $port : 3306;
		$this->connection = $this->dbConnect($servername, $port, $username, $password, $usepconnect, $charset);
		if ($this->connection) {
			$this->selectDB($this->database);
		}
		$this->writeUseTime($pagestarttime, "", "Connect DB:[$servername:$username:$database:$port:$charset]");
	}

	/**
	 * 动态应用平台数据库自动写连接
	 *
	 * @param String $charset
	 */
	function write($charset = '')
	{
		$this->connect($_SERVER['SINASRV_DB_NAME'], $_SERVER['SINASRV_DB_HOST'], $_SERVER['SINASRV_DB_USER'], $_SERVER['SINASRV_DB_PASS'], $_SERVER['SINASRV_DB_PORT'], false, $charset);
	}

	/**
	 * 动态应用平台数据库自动读连接
	 *
	 * @param String $charset
	 */
	function read($charset = '')
	{
		$this->connect($_SERVER['SINASRV_DB_NAME_R'], $_SERVER['SINASRV_DB_HOST_R'], $_SERVER['SINASRV_DB_USER_R'], $_SERVER['SINASRV_DB_PASS_R'], $_SERVER['SINASRV_DB_PORT_R'], false, $charset);
	}

	/**
	 * 根据config中database中的配置连接数据库
	 *
	 * @param unknown_type $params
	 */
	function configConnect($params)
	{
		$this->connect($params['database'], $params['hostname'], $params['username'], $params['password'], $params['port'], $params['pconnect'], $params['charset']);
	}

	/**
	 * 初始化数据库连接
	 *
	 * @param String $servername
	 * @param Integer $port
	 * @param String $username
	 * @param String $password
	 * @param Boolean $usepconnect
	 * @param String $charset
	 * @return Boolean
	 */
	function dbConnect($servername, $port, $username, $password, $usepconnect, $charset = '')
	{
		$connI	= 0;
		do {
			$link = $this->functions[$usepconnect ? 'pconnect' : 'connect']("$servername:$port", $username, $password);
			$connI++;
		}
		while ($link == false AND $this->reporterror AND $this->connTimes > $connI);
			restore_error_handler();

		if( FALSE == $link )
		{
			if( $this->reporterror )
			{
				$filename	= 'mysql_error_'.date('Ymd').'.log';
				$str		= date('y-m-d H:i:s') . "\tCan't connect to MySQL server $servername:$port.\n";
				$this->writeLogs( $filename , $str );
			}
		}
		else
		{
			if (!empty($charset)) {
				if (function_exists('mysql_set_charset')) {
					mysql_set_charset($charset, $link);
				} else {
					$this->sql = "SET NAMES $charset";
					$this->executeQuery(true, $link);
				}
			}
		}
		return $link;
	}

	/**
	 * 选择当前使用的数据库
	 *
	 * @param String $database
	 * @return Boolean
	 */
	function selectDB($database = '')
	{
		if ($database != '') {
			$this->database = $database;
		}
		if ($this->functions['selectDB']($this->database, $this->connection)) {
			return true;
		} else {
			$this->halt('Cannot use database ' . $this->database . '[File->'.__FILE__ . ";Function->" . __FUNCTION__ . ";Line->" . __LINE__.']');
			return false;
		}
	}

	/**
	 * 执行SQL语句，根据给定的数据库链接
	 *
	 * @param Boolean $buffered
	 * @param String $link
	 * @return String
	 */
	function executeQuery($buffered = true, $link)
	{
		if ($queryresult = $this->functions[$buffered ? 'query' : 'query_unbuffered']($this->sql, $link)) {
			$this->sql = '';
			return $queryresult;
		} else {
			$ret = $this->halt('[File->'.__FILE__ . ";Function->" . __FUNCTION__ . ";Line->" . __LINE__.']');
			$this->sql = '';
			return $ret;
		}
	}

	/**
	 * 根据已经建立的链接执行SQL语句
	 *
	 * @param String $sql
	 * @param Boolean $buffered
	 * @return String
	 */
	function query($sql, $buffered = true)
	{
		$pagestarttime = microtime();
		$this->sql = $sql;
		$ret = $this->executeQuery($buffered, $this->connection);
		$this->writeUseTime($pagestarttime, $sql, __FUNCTION__);
		unset($sql);
		return $ret;
	}

	/**
	 * 只返回所读取的数据库的第一列
	 *
	 * @param String $sql 需要执行的SELECT的SQL语句
	 * @param String $type 返回数组类型
	 * @return Array 取得的结果，数组
	 */
	function queryFirst($sql, $type = 'DBARRAY_ASSOC')
	{
		$pagestarttime = microtime();
		$this->sql = $sql;
		$queryresult = $this->executeQuery(true, $this->connection);
		$returnarray = $this->fetchArray($queryresult, $type);
		$this->freeResult($queryresult);
		$this->writeUseTime($pagestarttime, $sql, __FUNCTION__);
		unset($sql);
		return $returnarray;
	}
	/**
	 * 返回所读取的数据库的结果
	 *
	 * @param String $sql
	 * @param String $type
	 * @return Array 多维数组，可以使用foreach进行处理
	 */
	function queryArray($sql, $type = 'DBARRAY_ASSOC')
	{
		$pagestarttime = microtime();
		$this->sql = $sql;
		$queryresult = $this->executeQuery(true, $this->connection);
		$returnarray = array();
		while ($row = $this->fetchArray($queryresult, $type))
		{
			$returnarray[] = $row;
		}
		$this->freeResult($queryresult);
		$this->writeUseTime($pagestarttime, $sql, __FUNCTION__);
		unset($sql);
		return $returnarray;
	}

	/**
	 * 执行数据库插入操作
	 *
	 * @param array $queryvalues
	 * @param string $table
	 * @param boolean $buffered
	 * @return Integer 执行操作后返回的自动出入ID
	 */
	function queryInsert($queryvalues, $table, $buffered = true)
	{
		$pagestarttime = microtime();
		$this->sql = $this->fetchQuerySql($queryvalues, $table, '');
		$sql = $this->sql;
		$this->executeQuery($buffered, $this->connection);
		$id = $this->insertID();
		$this->writeUseTime($pagestarttime, $sql, __FUNCTION__);
		unset($sql);
		return $id;
	}

	/**
	 * 执行数据库替换操作
	 *
	 * @param array $queryvalues
	 * @param string $table
	 * @param boolean $buffered
	 * @return string
	 */
	function queryReplace($queryvalues, $table, $buffered = true)
	{
		$pagestarttime = microtime();
		$this->sql = $this->fetchQuerySql($queryvalues, $table, '', array(), true);
		$sql = $this->sql;
		$ret = $this->executeQuery($buffered, $this->connection);
		$this->writeUseTime($pagestarttime, $sql, __FUNCTION__);
		unset($sql);
		return $ret;
	}

	/**
	 * 执行数据库更新操作
	 *
	 * @param array $queryvalues
	 * @param string $table
	 * @param string $condition
	 * @param boolean $buffered
	 * @return string
	 */
	function queryUpdate($queryvalues, $table, $condition, $buffered = true)
	{
		if (empty($condition)) {
			return false;
		}
		$pagestarttime = microtime();
		$this->sql = $this->fetchQuerySql($queryvalues, $table, $condition);
		$sql = $this->sql;
		$ret = $this->executeQuery($buffered, $this->connection);
		$this->writeUseTime($pagestarttime, $sql, __FUNCTION__);
		unset($sql);
		return $ret;
	}

	/**
	 * 格式化数组为标准的SQL语句
	 *
	 * @param Array $queryvalues
	 * @param String $table
	 * @param String $condition
	 * @param Array $exclusions
	 * @param Boolean $replace
	 * @return String 标准的SQL语句
	 */
	function fetchQuerySql($queryvalues, $table, $condition = '', $exclusions = '', $replace = false)
	{
		if (empty($exclusions))
		{
			$exclusions = array();
		}
		$numfields = sizeof($queryvalues);
		$i = 1;
		if (!empty($condition))
		{
			$querystring = "";
			foreach($queryvalues AS $fieldname => $value)
			{
				$tempVal = $this->sqlPrepare($value);
				$querystring .= " `$fieldname`=" .$tempVal . (($i++ == $numfields) ? " " : ", ");
			}
			return "UPDATE $table SET $querystring $condition";
		} else {
			$fieldlist = '';
			$valuelist = '';
			foreach($queryvalues AS $fieldname => $value) {
				$endbit = $i++ == $numfields ? '' :  ', ';
				$fieldlist .= '`'.$fieldname.'`' . $endbit;
				$valuelist .= $this->sqlPrepare($value) . $endbit;
			}
			if ($replace) {
				return "REPLACE INTO $table ($fieldlist) VALUES ($valuelist)";
			}
			return "INSERT INTO $table ($fieldlist) VALUES ($valuelist)";
		}
	}

	/**
	 * 取得结果集中行的数目
	 *
	 * @param string $queryresult
	 * @return integer
	 */
	function numRows($queryresult)
	{
		return @$this->functions['numRows']($queryresult);
	}

	/**
	 * 取得结果集中字段的数目
	 *
	 * @param string $queryresult
	 * @return integer
	 */
	function numFields($queryresult)
	{
		return @$this->functions['numFields']($queryresult);
	}

	/**
	 *  取得结果中指定字段的字段名
	 *
	 * @param string $queryresult
	 * @param integer $index
	 * @return string
	 */
	function fieldName($queryresult, $index)
	{
		return @$this->functions['fieldName']($queryresult, $index);
	}

	/**
	 * 取得上一步 INSERT 操作产生的 ID
	 *
	 * @return integer
	 */
	function insertID()
	{
		return @$this->functions['insertID']($this->connection);
	}

	/**
	 * 从 MySQL 中取得 character_set 变量的值
	 *
	 * @return string 字符集的名称
	 */
	function clientEncoding()
	{
		return @$this->functions['clientEncoding']($this->connection);
	}

	/**
	 *  关闭 MySQL 连接
	 *
	 * @return integer
	 */
	function close()
	{
		return @$this->functions['close']($this->connection);
	}

	/**
	 * 本函数将 $string 转义，使之可以安全用于 mysql_query()。
	 *
	 * @param string $string
	 * @return string
	 */
	function escapeString($string)
	{
		if ($this->functions['escapeString'] == $this->functions['real_escape_string']) {
			return $this->functions['escapeString']($string, $this->connection);
		} else {
			return $this->functions['escapeString']($string);
		}
	}

	/**
	 * 处理查询语句需要出来的特殊字符， % 和 _
	 *
	 * @param string $string
	 * @return string
	 */
	function escapeStringLike($string)
	{
		return str_replace(array('%', '_') , array('\%' , '\_') , $this->escapeString($string));
	}

	/**
	 * 为SQL查询处理值
	 *
	 * @param mixed $value
	 * @return mixed
	 */
	function sqlPrepare($value)
	{
		if (is_string($value)) {
			return "'" . $this->escapeString($value) . "'";
		} else if (is_numeric($value) AND $value + 0 == $value) {
			return $value;
		} else if (is_bool($value)) {
			return $value ? 1 : 0;
		} else {
			return "'" . $this->escapeString($value) . "'";
		}
	}

	/**
	 * 返回根据从结果集取得的行生成的数组，如果没有更多行则返回 FALSE。
	 *
	 * @param string $queryresult
	 * @param integer $type
	 * @return array
	 */
	function fetchArray($queryresult, $type = 'DBARRAY_ASSOC')
	{
		return @$this->functions['fetchArray']($queryresult, $this->fetchtypes["$type"]);
	}

	/**
	 * 返回根据所取得的行生成的数组，如果没有更多行则返回 FALSE。
	 *
	 * @param string $queryresult
	 * @return array
	 */
	function fetchRow($queryresult)
	{
		return @$this->functions['fetchRow']($queryresult);
	}

	/**
	 * 从结果集中取得列信息并作为对象返回
	 *
	 * @param string $queryresult
	 * @return Object
	 */
	function fetchField($queryresult)
	{
		return @$this->functions['fetchField']($queryresult);
	}

	/**
	 * 将指定的结果标识所关联的 MySQL 结果内部的行指针移动到指定的行号。
	 *
	 * @param string $queryresult
	 * @param integer $index
	 * @return boolean
	 */
	function dataSeek($queryresult, $index)
	{
		return @$this->functions['dataSeek']($queryresult, $index);
	}

	/**
	 * 将释放所有与结果标识符 result 所关联的内存。
	 * 仅需要在考虑到返回很大的结果集时会占用多少内存时调用。在脚本结束后所有关联的内存都会被自动释放。
	 *
	 * @param string $queryresult
	 * @return boolean
	 */
	function freeResult($queryresult)
	{
		$this->sql = '';
		return @$this->functions['freeResult']($queryresult);
	}

	/**
	 * 取得前一次 MySQL 操作所影响的记录行数
	 *
	 * @return integer
	 */
	function affectedRows()
	{
		$this->rows = $this->functions['affectedRows']($this->connection);
		return $this->rows;
	}

	/**
	 * 返回上一个 MySQL 函数的错误文本，如果没有出错则返回 ''（空字符串）。
	 *
	 * @return string
	 */
	function error()
	{
		if ($this->connection === null) {
			$this->error = '';
		} else {
			$this->error = $this->functions['error']($this->connection);
		}
		return $this->error;
	}

	/**
	 * 返回上一个 MySQL 函数的错误号码，如果没有出错则返回 0（零）。
	 *
	 * @return integer
	 */
	function errno()
	{
		if ($this->connection === null) {
			$this->errno = 0;
		} else {
			$this->errno = $this->functions['errno']($this->connection);
		}
		return $this->errno;
	}

	/**
	 * 显示数据库报错信息
	 *
	 */
	function showErrors()
	{
		$this->reporterror = true;
	}

	/**
	 * 隐藏数据库报错信息
	 *
	 */
	function hideErrors()
	{
		$this->reporterror = false;
	}

	/**
	 * 处理数据库出错信息，并且打印出错日志
	 *
	 * @param string $errortext
	 * @return boolean
	 */
	function halt($errortext = '')
	{
		$this->error = $this->error($this->connection);
		$this->errno = $this->errno($this->connection);
		if ($this->logerrors) {
    		$outStr = "Invalid SQL:" . $this->sql. "\tMySQL Error:" .$this->errno . "(" . $this->error. ")\t" . getenv('REMOTE_ADDR') . "\t" . date("Y-m-d H:i:s") ."\t". $errortext ."\n";
    		$this->writeLogs("mysql_error_".date("Ymd").".log", $outStr);
		}
		if ($this->reporterror)
		{
			if ($errortext == '') {
				$this->sql = "Invalid SQL:\r\n" . chop($this->sql) . ';';
				$errortext =& $this->sql;
			}
			if (!headers_sent()) {
				$sapiname = php_sapi_name();
				if ($sapiname == 'cgi' OR $sapiname == 'cgi-fcgi') {
					header('Status: 503 Service Unavailable');
				} else {
					header('HTTP/1.1 503 Service Unavailable');
				}
			}
			$this->hideErrors();
			list($mysqlversion) = $this->queryFirst("SELECT VERSION() AS version", DBARRAY_NUM);
			$this->showErrors();
			$message = "MySQL Version:".$mysqlversion."\r\n";
			$message .= $errortext."\r\n";
			$message .= "MySQL Error:".$this->errno."(".$this->error.")\r\n";
			//die($message);
		} else if (!empty($errortext)) {
			$this->error = $errortext;
		}
		return false;
	}

	/**
	 * 日志文件写
	 *
	 * @param String $filename
	 * @param String $str
	 * @return Boolean
	 */
	function writeLogs($filename, $str)
	{
		if (isset($_SERVER['SINASRV_APPLOGS_DIR'])) {
			$thisfile = $_SERVER['SINASRV_APPLOGS_DIR'];
		} else {
			$thisfile = $this->errorpath;
		}
		$str = trim($str);
		if (strlen($str) == 0) {
			return false;
		}
    	$thisfile .= $filename;
    	$file_pointer = @fopen($thisfile, "a");
    	@fwrite($file_pointer, $str."\n");
    	@fclose($file_pointer);
    	return true;
	}

	/**
	 * 记录数据库执行需要的时间日志
	 *
	 * @param String $pagestarttime
	 * @param String $sql
	 * @param String $ext
	 * @return Boolean
	 */
	function writeUseTime($pagestarttime, $sql, $ext = "")
	{
		if (false === $this->usetime) {
			return false;
		}
		$starttime = explode(' ', $pagestarttime);
		$endtime = explode(' ', microtime());
		$totaltime = $endtime[0] - $starttime[0] + $endtime[1] - $starttime[1];
		$usedtime = number_format($totaltime, 5);
	    $outStr = "Mysql used times \t".$usedtime."\tSQL:".$sql."\t".$ext."\t".getenv('REMOTE_ADDR')."\t".date("Y-m-d H:i:s")."\n";
	    $this->writeLogs("mysql_used_time_".date("Ymd").".log", $outStr);
	    return true;
	}
}

?>